﻿/****** Object:  StoredProcedure [dbo].[LeagueInfoInsert]    Script Date: 10/28/2011 11:06:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LeagueInfoInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LeagueInfoInsert]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LeagueInfoInsert] 
	-- Add the parameters for the stored procedure here
	@LeagueName VARCHAR(50),
	@SeasonYear SMALLINT,
	@Note VARCHAR(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @leagueID UNIQUEIDENTIFIER;
	SELECT @leagueID = LeagueID FROM LeagueInfo 
	WHERE LeagueName = @LeagueName AND SeasonYear = @SeasonYear;
	
	IF @leagueID IS NULL
	BEGIN
		SELECT @leagueID = NEWID();
		INSERT INTO LeagueInfo(LeagueID, SeasonYear, LeagueName, Note) 
			VALUES(@leagueID, @SeasonYear, @LeagueName, @Note);
	END
	SELECT @leagueID AS LeagueID;
END

GO


/****** Object:  StoredProcedure [dbo].[LeagueMatchInsert]    Script Date: 10/28/2011 11:43:07 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LeagueMatchInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LeagueMatchInsert]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LeagueMatchInsert]
	-- Add the parameters for the stored procedure here
	@LeagueID VARCHAR(50),
	@HomeTeamID VARCHAR(50),
	@VisitingTeamID VARCHAR(50),
	@RoundNumber TINYINT,
	@MatchDateTime DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @matchID UNIQUEIDENTIFIER;
    
	SELECT @matchID = m.MatchID 
	FROM LeagueMatch m	
	WHERE m.LeagueID = @LeagueID
		AND m.RoundNumber = @RoundNumber
		AND m.HomeTeamID = @HomeTeamID
		AND m.VisitingTeamID = @VisitingTeamID
	
	IF @matchID IS NULL BEGIN
		SELECT @matchID = NEWID();
		INSERT INTO LeagueMatch(MatchID,LeagueID,HomeTeamID,VisitingTeamID,RoundNumber,MatchDateTime) 
			VALUES(@matchID,@LeagueID,@HomeTeamID,@VisitingTeamID,@RoundNumber,@MatchDateTime)
	END
	SELECT @matchID as MatchID;
END

GO


/****** Object:  StoredProcedure [dbo].[LeagueTeamDataInsert]    Script Date: 11/11/2011 01:59:09 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LeagueTeamDataInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LeagueTeamDataInsert]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[LeagueTeamDataInsert]
	-- Add the parameters for the stored procedure here
	@MatchID UNIQUEIDENTIFIER,
	@TeamID UNIQUEIDENTIFIER,
	@IsAtHome BIT,
	@Goal TINYINT,
	@Shot TINYINT,
	@ShotOnTarget TINYINT,
	@BlockedShot TINYINT,
	@Corner TINYINT,
	@FreeKick TINYINT,
	@OutOfBounds TINYINT,
	@Foul TINYINT,
	@Offside TINYINT,
	@BallControl DECIMAL(5,2),
	@PassSuccess DECIMAL(5,2),
	@PassMiddleSuccess DECIMAL(5,2),
	@TacklingSuccess DECIMAL(5,2),
	@HeadSuccess DECIMAL(5,2),
	@YellowCard TINYINT,
	@RedCard TINYINT	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @teamDataID UNIQUEIDENTIFIER;
	
	SELECT @teamDataID = TeamDataID FROM LeagueTeamData 
	WHERE MatchID = @MatchID AND TeamID = @TeamID;
		
	IF @teamDataID IS NULL BEGIN
		SELECT @teamDataID = NEWID();
		INSERT INTO LeagueTeamData(TeamDataID, MatchID, TeamID, IsAtHome, Goal, Shot, ShotOnTarget, 
			BlockedShot, Corner, FreeKick, OutOfBounds, Foul, Offside, BallControl, 
			PassSuccess, PassMiddleSuccess,	TacklingSuccess, HeadSuccess, YellowCard, RedCard)
		VALUES(@teamDataID, @MatchID, @TeamID, @IsAtHome, @Goal, @Shot, @ShotOnTarget, @BlockedShot, 
			@Corner, @FreeKick, @OutOfBounds, @Foul, @Offside, @BallControl, @PassSuccess, 
			@PassMiddleSuccess,	@TacklingSuccess, @HeadSuccess, @YellowCard, @RedCard);
	END ELSE BEGIN 
		UPDATE LeagueTeamData SET MatchID=@MatchID, TeamID=@TeamID, IsAtHome=@IsAtHome, Goal=@Goal, 
			Shot=@Shot,	ShotOnTarget=@ShotOnTarget, BlockedShot=@BlockedShot, Corner=@Corner,
			FreeKick=@FreeKick, OutOfBounds=@OutOfBounds, Foul=@Foul, Offside=@Offside,
			BallControl=@BallControl, PassSuccess=@PassSuccess, PassMiddleSuccess=@PassMiddleSuccess,
			TacklingSuccess=@TacklingSuccess, HeadSuccess=@HeadSuccess, YellowCard=@YellowCard, 
			RedCard=@RedCard, UpdateTime=GETDATE()  
		WHERE TeamDataID = @teamDataID;
	END
	
    -- Insert statements for procedure here
	SELECT @teamDataID AS TeamDataID;
END

GO

